package com.itheima.Mapper;

import com.itheima.Pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface EmpMapper {

        /*删除数据
          @Delete("delete  from  emp where id=6")
          public void delete();*/
        //使用#{}代替具体数据,灵活输入,防止sql注入.${}直接把数据拼接,不安全
        @Delete("delete  from  emp where id=#{id}")
        public void delete(Integer id);

        //添加数据
        //使用@Options,将自动生成的主键值传给对象中的id属性
        @Insert("insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
                "values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime});")
        @Options(keyProperty = "id", useGeneratedKeys = true)
        public void inset(Emp emp);

        //更新数据
        @Update("update emp set username=#{username},name=#{name},gender=#{gender},image=#{image},job=#{job}," +
                "entrydate=#{entrydate},dept_id=#{deptId},update_time=#{updateTime} where id=#{id};")
        public void update(Emp emp);

        //查询数据
        @Select("select id ,username, password,name, gender, image, job, entrydate, dept_id, create_time, update_time from emp where id=#{id};")
        public Emp select(Integer id);
        /*
        实体类中属性名与数据库字段名一致,会自动封装,否则要手动封装
        方法1:起别名
        @Select("select id ,username, password,name, gender, image, job, entrydate, dept_id deptId , create_time createTime, update_time updateTime from emp where id=#{id};")
        public Emp select(Integer id);

        方法二:使用注解@Results({ @Result(数据库字段, 实体类属性名)})
        @Results({
                @Result(column ="dept_id",property = "deptId"),
                @Result(column ="create_time",property = "createTime"),
                @Result(column ="update_time",property = "updateTime")

        })
        @Select("select id ,username, password,name, gender, image, job, entrydate, dept_id, create_time, update_time from emp where id=#{id};")
        public Emp select(Integer id);

        方法三:开启mybatis驼峰映射配置,自动封装a_column---->aColumn
        mybatis.configuration.map-underscore-to-camel-case=true
         */

        //存在sql注入,不安全
       /* @Select("select *from emp where name like '%${name}%' and gender=#{gender} and  entrydate " +
                "between #{begin} and #{end} order  by update_time desc ;")
        public List<Emp> selectTest2(String name, Short gender, LocalDate begin, LocalDate end);*/
        //在引号里需要使用#{}时,使用concat()拼接字符串
       @Select("select *from emp where name like concat('%',#{name},'%') and gender=#{gender} and  entrydate " +
               "between #{begin} and #{end} order  by update_time desc ;")
       //低版本springboot,参数需要使用注解@Param命名
       //public List<Emp> selectTest2(@Param("name")String name, @Param("gender")Short gender, @Param("begin")LocalDate begin, @Param("end")LocalDate end);
         public List<Emp> selectTest2(String name, Short gender, LocalDate begin, LocalDate end);


       //使用xml文件配置sql语句
       /*
            1.xml文件需要名称需要与Mapper接口名一致,并存放在resources下的相同目录
            2.xml的Mapper方法里的namespace属性与Mapper接口全类名一致
            3.xml文件里的sql语句id属性与Mapper接口里的方法名一致,resultType属性与方法返回值类型相同
       */
       //动态查询
        public List<Emp> xmlSelect(String name, Short gender, LocalDate begin, LocalDate end);

        //动态更新
        public void update2(Emp emp);

        //批量删除
        public void delete2(List<Integer> idList);
}